package com.jlu.poi;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileInputStream;
import java.util.Date;

public class ExcelReadTest {


  //  03版excel
  @Test
  public void read03() throws Exception {

//    拿到文件
    FileInputStream fileInputStream = new FileInputStream("D:\\BaiduNetdiskDownload\\POI&EasyExcel\\POI\\src\\main\\resources\\BigData03.xls");
//    获取一个工作簿
    Workbook workbook = new HSSFWorkbook(fileInputStream);
//    获取一个工作表
    Sheet sheet = workbook.getSheetAt(0);
//    获取值
    Row row = sheet.getRow(0);
//    读取时一定要注意类型
    Cell cell = row.getCell(1);
    System.out.println(cell);
    fileInputStream.close();
  }

  //  07版excel
  @Test
  public void read07() throws Exception {

//    拿到文件
    FileInputStream fileInputStream = new FileInputStream("D:\\BaiduNetdiskDownload\\POI&EasyExcel\\POI\\src\\main\\resources\\BigData07S.xlsx");
//    获取一个工作簿
    Workbook workbook = new XSSFWorkbook(fileInputStream);
//    获取一个工作表
    Sheet sheet = workbook.getSheetAt(0);
//    获取值
    Row row = sheet.getRow(0);
//    读取时一定要注意类型
    Cell cell = row.getCell(0);
    System.out.println(cell);
    fileInputStream.close();
  }

  //  07版excel
  @Test
  public void readCellType() throws Exception {

//    拿到文件
    FileInputStream fileInputStream = new FileInputStream("D:\\BaiduNetdiskDownload\\POI&EasyExcel\\POI\\src\\main\\resources\\工作表.xls");
//    获取一个工作簿
    Workbook workbook = new HSSFWorkbook(fileInputStream);
//    获取一个工作表
    Sheet sheet = workbook.getSheetAt(0);
//    获取标题
    Row rowTitle = sheet.getRow(0);
    if (rowTitle != null){
//      拿到一行都多少列
      int number = rowTitle.getPhysicalNumberOfCells();
      for (int cellnumber = 0; cellnumber < number; cellnumber++) {
        Cell cell = rowTitle.getCell(cellnumber);
        if (cell != null){
//          int cellType = cell.getCellType();
          String cellValue = cell.getStringCellValue();
          System.out.print(cellValue + "|");
        }
      }
      System.out.println();
    }

//    获取数据
    int rowCount = sheet.getPhysicalNumberOfRows();
    for (int rowNum = 1;rowNum < rowCount;rowNum++){
      Row rowData = sheet.getRow(rowNum);
      if (rowData != null){
        int cellCount = rowTitle.getPhysicalNumberOfCells();
        for (int cellNum = 0 ;cellNum < cellCount;cellNum++){

          Cell cell = rowData.getCell(cellNum);
          if (cell != null){
            int cellType = cell.getCellType();
            String cellValue = "";

            switch (cellType){
              case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                System.out.print("【Boolean】");
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
              case HSSFCell.CELL_TYPE_STRING://字符串
                System.out.print("【String】");
                cellValue = cell.getStringCellValue();
                break;
              case HSSFCell.CELL_TYPE_NUMERIC://数字
                System.out.print("【NUMERIC】");
                if (HSSFDateUtil.isCellDateFormatted(cell)){
                  Date date= cell.getDateCellValue();
                  cellValue = new DateTime(date).toString("yyyy-MM-dd HH:mm:ss");
                }else {
//                  不是日期
                  System.out.print("【转换为字符串输出】");
                  cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                  cellValue = cell.toString();
                }
                break;
              case HSSFCell.CELL_TYPE_ERROR://错误
                System.out.print("【Error】");
                break;
            }
            System.out.println(cellValue);
          }
        }
      }

    }
    fileInputStream.close();
  }

  @Test
  public void formula() throws Exception{
    FileInputStream fileInputStream = new FileInputStream("D:\\BaiduNetdiskDownload\\POI&EasyExcel\\POI\\src\\main\\resources\\计算公式.xls");
    Workbook workbook = new HSSFWorkbook(fileInputStream);
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(4);
    Cell cell = row.getCell(0);

//    拿到计算公式
    FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook)workbook);

//    输出单元格的内容
    int cellType = cell.getCellType();

    switch (cellType){
      case Cell.CELL_TYPE_FORMULA://公式
        String formula = cell.getCellFormula();
        System.out.println(formula);

//        计算
        CellValue evaluate = formulaEvaluator.evaluate(cell);
        System.out.println(evaluate.formatAsString());
        break;
    }

  }
}
